from datetime import datetime
from sqlalchemy import DateTime, create_engine, Column, Integer, String, Text, text
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
import os

Base = declarative_base()

# SQLite 本身不支持用户名和密码。它是一个轻量级的、基于文件的数据库，通常用于单用户应用程序或嵌入式系统。
class DbUtil:
    def __init__(self, db_name='sqlite_database', root_path=os.getcwd(), **kwargs):
        """
        初始化 SQLite 数据库连接 
        :param db_name: 数据库名称 (默认为 'sqlite_database')
        :param root_path: 数据库文件存储根路径
        :param kwargs: 其他参数 (SQLite 不需要)
        """
        self.engine = self._create_engine(db_name, root_path)
        self.Session = sessionmaker(bind=self.engine)
        Base.metadata.create_all(self.engine)  # 创建表 (如果不存在)

    def _create_engine(self, db_name, root_path):
        # SQLite 连接字符串
        db_path = os.path.join(root_path, f"{db_name}.db")  # 使用 .db 后缀
        return create_engine(f"sqlite:///{db_path}")

    # 其余 CRUD 和 execute_sql 方法保持不变
    def insert(self, model_instance):
        """插入数据"""
        session = self.Session()
        session.add(model_instance)
        session.commit()
        session.refresh(model_instance)
        return model_instance

    def select(self, model_class, **filters):
        """查询数据"""
        session = self.Session()
        query = session.query(model_class)
        for key, value in filters.items():
            query = query.filter(getattr(model_class, key) == value)
        return query.all()

    def update(self, model_class, updates, **filters):
        """更新数据"""
        session = self.Session()
        query = session.query(model_class)
        for key, value in filters.items():
            query = query.filter(getattr(model_class, key) == value)
        query.update(updates)
        session.commit()

    def delete(self, model_class, **filters):
        """删除数据"""
        session = self.Session()
        query = session.query(model_class)
        for key, value in filters.items():
            query = query.filter(getattr(model_class, key) == value)
        query.delete()
        session.commit()

    def execute_sql(self, sql):
        """执行原始SQL"""
        session = self.Session()
        try:
            result = session.execute(text(sql))
            session.commit()
            return result.fetchall()  # 如果有结果的话
        except Exception as e:
            session.rollback()
            raise e
        finally:
            session.close()

    def get_user_by_name(self, username):
        """根据用户名获取用户信息"""
        session = self.Session()
        user = session.query(User).filter(User.name == username).first()
        return user
    
    def get_resource_list(self):
        """查询所有有效的影视资源"""
        session = self.Session()
        try:
            movies = session.query(Movie).filter(Movie.is_active == 1).all()
            resources = []
            for movie in movies:
                resources.append({
                    "name": movie.name,
                    "desc": movie.desc,
                    "url": movie.url,
                    "play_url": movie.play_url
                })
            return resources
        except Exception as e:
            print(f"查询数据库出错: {e}")  # 错误处理
            return [] # 返回空列表，避免程序崩溃
        finally:
            session.close()


# 示例模型 (保持不变)
class User(Base):
    __tablename__ = 'users'
    id = Column(Integer, primary_key=True)
    name = Column(String(20), nullable=False)
    email = Column(String(50))
    password = Column(String(50))
    description = Column(String(100))
    is_active = Column(Integer, default=0)
    created_at = Column(DateTime, default=datetime.now)
    updated_at = Column(DateTime)

# 影视资源模型
class Movie(Base):
    __tablename__ = 'movies'
    id = Column(Integer, primary_key=True)
    name = Column(String(255), nullable=False)  # 资源名称
    desc = Column(Text)  # 资源描述
    url = Column(String(255))  # 采集 URL
    play_url = Column(String(255))  # 播放器 URL
    is_active = Column(Integer, default=0)  # 是否有效
    created_at = Column(DateTime, default=datetime.now)  # 创建时间
    updated_at = Column(DateTime)  # 更新时间


if __name__ == "__main__":
    # 初始化 SQLite 数据库连接
    db_util = DbUtil(db_name='sqlite_database', root_path=os.getcwd())

    # 添加多个用户信息adong,admin，密码都是123456
    users_data = [
        {"name": "adong", "password": "123456", "description": "用户adong", "email": "adong@example.com", "is_active": 1},
        {"name": "admin", "password": "123456", "description": "用户admin", "email": "admin@example.com", "is_active": 1},
    ]
    for user_data in users_data:
        existing_user = db_util.get_user_by_name(user_data['name'])
        if not existing_user:  # 仅当用户不存在时才插入
            new_user = User(**user_data)
            db_util.insert(new_user)
            print(f"插入用户：{user_data['name']}")
        else:
            print(f"用户已存在：{user_data['name']}")

    # 添加多个影视资源，名称唯一
    movies_data = [
        {"name": "heimue", "desc": "木耳", "url": "https://json02.heimuer.xyz/api.php/provide/vod/", "play_url": "https://hoplayer.com/index.html?url="},
        {"name": "haohua", "desc": "豪华", "url": "https://hhzyapi.com/api.php/provide/vod/from/hhm3u8/at/json/", "play_url": "https://hhjiexi.com/play/?url="},
        {"name": "feifan", "desc": "非凡", "url": "http://cj.ffzyapi.com/api.php/provide/vod/from/ffm3u8/at/json/", "play_url": "https://svip.ffzyplay.com/?url="},
    ]

    for movie_data in movies_data:
        existing_movie = db_util.select(Movie, name=movie_data['name'])
        if not existing_movie:  # 仅当资源不存在时才插入
            new_movie = Movie(**movie_data)
            db_util.insert(new_movie)
            print(f"插入影视资源：{movie_data['name']}")
        else:
            print(f"影视资源已存在：{movie_data['name']}")

    # 示例 CRUD 操作 (可以根据需要修改)
    """
        new_user = User(name='Bob', password='password456', description='SQLite User', email='bob@example.com', is_active=1)
        inserted_user = db_util.insert(new_user)
        print(f"Inserted user: {inserted_user.id}, {inserted_user.name}")

        users = db_util.select(User, name='Bob')
        for user in users:
            print(f"Found user: {user.id}, {user.name}, {user.email}")

        db_util.update(User, {'description': 'Updated SQLite User'}, name='Bob')
        updated_users = db_util.select(User, name='Bob')
        for user in updated_users:
            print(f"Updated user: {user.id}, {user.description}")

        db_util.delete(User, name='Bob')
        deleted_users = db_util.select(User, name='Bob')
        print(f"Number of users after deletion: {len(deleted_users)}")

        result = db_util.execute_sql("SELECT * FROM users")
        print(f"SQL query result: {result}")
    """
